unit DataModule;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, mssqlconn, sqldb, fgl, SyncObjs, FileUtil, fpjson, jsonparser, UCommon;
const
  //MaxDouble = 1.7e+308;
  ALIVE_SEC = 60;       // 超时(10秒)
  TOSECONDS = 86400;     // 转换成秒数的倍率(60*60*24)
  OVERTIME: double = (ALIVE_SEC / TOSECONDS);

type
  PQUERYPACK = ^TQUERYPACK;
  TQUERYPACK = record
    Query: TSQLQuery;
    Trans: TSQLTransaction;
    Atime: TDateTime;  // 超时
  end;

  // 泛型, 申明一个List类型
  TQueryList = specialize TFPGList<PQUERYPACK>;

  { TDM }

  TDM = class(TDataModule)
    MSSQLConn: TMSSQLConnection;
    Conkaoqing: TMSSQLConnection;
    procedure DataModuleCreate(Sender: TObject);
    procedure DataModuleDestroy(Sender: TObject);
    function GetIndexQueryList(Sender: TObject): Integer;
    procedure DisIndexQueryList(Index: Integer);
    function GetIndexResdbList(Sender: TObject): Integer;
    procedure DisIndexResdbList(Index: Integer);

    procedure GetUserInfo(soc: DWord; jsParam: TJSONObject);
    procedure GetKaoqing(soc: DWord; jsParam: TJSONObject);
    procedure GetCustomer(soc: DWord; jsParam: TJSONObject);
    procedure GetCustInfo(soc: DWord; jsParam: TJSONObject);
  private

  public
    LockQList, LockRList: TCriticalSection;
    QueryList, ResdbList: TQueryList;

  end;

var
  DM: TDM;

implementation
uses socketserver, MainctrlFrm;
{$R *.lfm}

{ TDM }

function TDM.GetIndexQueryList(Sender: TObject): Integer;
var
  idx: Integer;
  pqr: PQUERYPACK;
begin
  try
    LockQList.Enter;
    Result := -1;
    for idx:=0 to QueryList.Count-1 do
    begin
      pqr := QueryList[idx];
      if ((Now() - pqr^.ATime) >= OVERTIME) then
      begin
        pqr^.Atime := Now();
        pqr^.Query.Close;
        Result := idx;
        Break;
      end;
    end;
    if Result = -1 then
    begin
      New(pqr);
      pqr^.Trans := TSQLTransaction.Create(nil);
      pqr^.Trans.DataBase := MSSQLConn;
      pqr^.Query := TSQLQuery.Create(nil);
      pqr^.Query.DataBase := MSSQLConn;
      pqr^.Query.Transaction := pqr^.Trans;
      pqr^.Atime := Now();
      Result := QueryList.Add(pqr);
    end;
    if not MSSQLConn.Connected then
      MSSQLConn.Connected := True;
  finally
    LockQList.Leave; // 解锁
  end;
end;

procedure TDM.DisIndexQueryList(Index: Integer);
begin
  QueryList[Index]^.Atime := 0;
end;

function TDM.GetIndexResdbList(Sender: TObject): Integer;
var
  idx: Integer;
  pqr: PQUERYPACK;
begin
  try
    LockRList.Enter;
    Result := -1;
    for idx:=0 to ResdbList.Count-1 do
    begin
      pqr := ResdbList[idx];
      if ((Now() - pqr^.ATime) >= OVERTIME) then
      begin
        pqr^.Atime := Now();
        pqr^.Query.Close;
        Result := idx;
        Break;
      end;
    end;
    if Result = -1 then
    begin
      New(pqr);
      pqr^.Trans := TSQLTransaction.Create(nil);
      pqr^.Trans.DataBase := Conkaoqing;
      pqr^.Query := TSQLQuery.Create(nil);
      pqr^.Query.DataBase := Conkaoqing;
      pqr^.Query.Transaction := pqr^.Trans;
      pqr^.Atime := Now();
      Result := ResdbList.Add(pqr);
    end;
    if not Conkaoqing.Connected then
      Conkaoqing.Connected := True;
  finally
    LockRList.Leave; // 解锁
  end;
end;

procedure TDM.DisIndexResdbList(Index: Integer);
begin
  ResdbList[Index]^.Atime := 0;
end;

procedure TDM.DataModuleCreate(Sender: TObject);
begin
  LockQList := TCriticalSection.Create;
  LockRList := TCriticalSection.Create;
  QueryList := TQueryList.Create;
  ResdbList := TQueryList.Create;
end;

procedure TDM.DataModuleDestroy(Sender: TObject);
var
  idx: Integer;
  pqr: PQUERYPACK;
begin
  LockQList.Free;
  LockRList.Free;
  for idx:=0 to QueryList.Count-1 do
  begin
    pqr := QueryList[idx];
    pqr^.Query.Free;
    pqr^.Trans.Free;
    Dispose(pqr);
  end;
  QueryList.Clear;
  FreeAndNil(QueryList);
  for idx:=0 to ResdbList.Count-1 do
  begin
    pqr := ResdbList[idx];
    pqr^.Query.Free;
    pqr^.Trans.Free;
    Dispose(pqr);
  end;
  ResdbList.Clear;
  FreeAndNil(ResdbList);
end;

procedure TDM.GetUserInfo(soc: DWord; jsParam: TJSONObject);
var
  Usid, Pass, UserName: string;
  idx : Integer;
  qry: TSQLQuery;
begin
  Usid := jsParam.Get('PMA');
  Pass := jsParam.Get('PMB');
  UserName := '';
  if (Pos('%',Usid)>0) or (Pos('%',Pass)>0) or (Pos('=',Usid)>0) or (Pos('=',Pass)>0)
     or (Pos('(',Usid)>0) or (Pos(')',Usid)>0) or (Pos('(',Pass)>0) or (Pos(')',Pass)>0) then
  begin
    Exit;
  end;
  idx := GetIndexQueryList(Self);
  qry := QueryList[idx]^.Query;
  with qry do
  begin
    Close;
    Params.Clear;
    SQL.Text := 'SELECT * FROM TB_User WHERE UserId=:uid AND UserPass=:psw';
    ParamByName('uid').AsString := Usid;
    ParamByName('psw').AsString := Pass;
    Open;
    if RecordCount <= 0 then Exit;
    UserName := FieldByName('UserName').AsString;
    MainCtrlForm.Memo.Lines.Add(UserName);
  end;
  DisIndexQueryList(idx);
  if UserName<>'' then
    iocPost(soc,PChar(UserName),length(UserName));
end;

procedure TDM.GetKaoqing(soc: DWord; jsParam: TJSONObject);
var
  UserId, CardID, EmplID: string;
  js, jObject : TJSONObject;
  jArray : TJSONArray;
  gzs: AnsiString;
  idx : Integer;
  qry: TSQLQuery;
const
  ts='{"DATE":"","WEEK":"","NAME":"","T2":"","T3":"","T4":"","T5":""}';
begin
  UserId := jsParam.Get('PMA');
  if Length(UserId) = 3 then
    CardID := '00' + UserId
  else if Length(UserId) = 4 then
    CardID := '0' + UserId
  else Exit;
  jArray := TJSONArray.Create;
  idx := GetIndexResdbList(Self);
  qry := ResdbList[idx]^.Query;
  with qry do
  begin
    Close;
    Params.Clear;
    SQL.Text := 'SELECT EmplID FROM HrEmployee WHERE CardID LIKE :cid';
    ParamByName('cid').AsString := CardID;
    Open;
    if FieldByName('EmplID').IsNull then Exit;
    EmplID := FieldByName('EmplID').AsString;
    //
    Close;
    Params.Clear;
    SQL.Text := 'EXEC sp_GetSigntime2 :eid';
    ParamByName('eid').AsString := EmplID;
    Open;
    while not Eof do
    begin
      js := TJSONObject(GetJSON(ts));
      js.Strings['DATE'] := Copy(FieldByName('sr_date').AsString,6,5);
      js.Strings['WEEK'] := FieldByName('sr_week').AsString;
      js.Strings['NAME'] := FieldByName('empname').AsString;
      js.Strings['T2'] := FieldByName('s_time2').AsString;
      js.Strings['T3'] := FieldByName('s_time3').AsString;
      js.Strings['T4'] := FieldByName('s_time4').AsString;
      js.Strings['T5'] := FieldByName('s_time5').AsString;
      jArray.Add(js);
      Next;
    end;
  end;
  DisIndexResdbList(idx);
  jObject := TJSONObject.Create;
  jObject.Add('T', jArray);
  gzs := TFun.GzpCompress(jObject.AsJSON) + '$END#';
  iocPost(soc, PChar(gzs), Length(gzs));
end;

procedure TDM.GetCustomer(soc: DWord; jsParam: TJSONObject);
var
  js, jObject : TJSONObject;
  jArray : TJSONArray;
  gzs: AnsiString;
  idx: Integer;
  qry: TSQLQuery;
const
  ts='{"IDD":"","CUSTOMER":"","ADDRESS":"","MEMO":""}';
begin
  jArray := TJSONArray.Create;
  idx := GetIndexQueryList(Self);
  qry := QueryList[idx]^.Query;
  with qry do
  begin
    Close;
    SQL.Text :='SELECT idd,customer,address,memo FROM PM_Customer '
              +' WHERE customer IN(SELECT DISTINCT CustomerName FROM PM_COrder WHERE indate>DATEADD(MONTH,-3,GETDATE())) '
              +' ORDER BY customer ';
    Open;
    while not Eof do
    begin
      js := TJSONObject(GetJSON(ts));
      js.Strings['IDD'] := FieldByName('idd').AsString;
      js.Strings['CUSTOMER'] := FieldByName('customer').AsString;
      js.Strings['ADDRESS'] := FieldByName('address').AsString;
      js.Strings['MEMO'] := FieldByName('memo').AsString;
      jArray.Add(js);
      Next;
    end;
  end;
  DisIndexQueryList(idx);
  jObject := TJSONObject.Create;
  jObject.Add('T', jArray);
  gzs := TFun.GzpCompress(jObject.AsJSON) + '$CUS#';
  iocPost(soc, PChar(gzs), Length(gzs));
end;

procedure TDM.GetCustInfo(soc: DWord; jsParam: TJSONObject);
var
  jObject : TJSONObject;
  gzs: AnsiString;
  idx: Integer;
  qry: TSQLQuery;
  cus: string;
begin
  cus := jsParam.Get('PMA','');
  if (''=cus) then Exit;
  jObject := TJSONObject.Create;
  idx := GetIndexQueryList(Self);
  qry := QueryList[idx]^.Query;
  with qry do
  begin
    Close;
    Params.Clear;
    SQL.Text :='SELECT * FROM PM_Customer WHERE customer LIKE :cus';
    ParamByName('cus').AsString := cus;
    Open;
    if RecordCount <= 0 then Exit;
    jObject.Add('TEL', FieldByName('Tel').AsString);
    jObject.Add('FAX', FieldByName('Fax').AsString);
    // 1
    jObject.Add('LINK1TYPE', FieldByName('Link1Type').AsString);
    jObject.Add('LINK1NAME', FieldByName('Link1Name').AsString);
    jObject.Add('LINK1TEL', FieldByName('Link1Tel').AsString);
    jObject.Add('LINK1TELEXT', FieldByName('Link1TelExt').AsString);
    jObject.Add('LINK1MOBILE', FieldByName('Link1Mobile').AsString);
    jObject.Add('LINK1EMAIL', FieldByName('Link1Email').AsString);
    jObject.Add('LINK1MEMO', FieldByName('Link1MEMO').AsString);
    // 2
    jObject.Add('LINK2TYPE', FieldByName('Link2Type').AsString);
    jObject.Add('LINK2NAME', FieldByName('Link2Name').AsString);
    jObject.Add('LINK2TEL', FieldByName('Link2Tel').AsString);
    jObject.Add('LINK2TELEXT', FieldByName('Link2TelExt').AsString);
    jObject.Add('LINK2MOBILE', FieldByName('Link2Mobile').AsString);
    jObject.Add('LINK2EMAIL', FieldByName('Link2Email').AsString);
    jObject.Add('LINK2MEMO', FieldByName('Link2MEMO').AsString);
    // 3
    jObject.Add('LINK3TYPE', FieldByName('Link3Type').AsString);
    jObject.Add('LINK3NAME', FieldByName('Link3Name').AsString);
    jObject.Add('LINK3TEL', FieldByName('Link3Tel').AsString);
    jObject.Add('LINK3TELEXT', FieldByName('Link3TelExt').AsString);
    jObject.Add('LINK3MOBILE', FieldByName('Link3Mobile').AsString);
    jObject.Add('LINK3EMAIL', FieldByName('Link3Email').AsString);
    jObject.Add('LINK3MEMO', FieldByName('Link3MEMO').AsString);
    // 4
    jObject.Add('LINK4TYPE', FieldByName('Link4Type').AsString);
    jObject.Add('LINK4NAME', FieldByName('Link4Name').AsString);
    jObject.Add('LINK4TEL', FieldByName('Link4Tel').AsString);
    jObject.Add('LINK4TELEXT', FieldByName('Link4TelExt').AsString);
    jObject.Add('LINK4MOBILE', FieldByName('Link4Mobile').AsString);
    jObject.Add('LINK4EMAIL', FieldByName('Link4Email').AsString);
    jObject.Add('LINK4MEMO', FieldByName('Link4MEMO').AsString);
    // 5
    jObject.Add('LINK5TYPE', FieldByName('Link5Type').AsString);
    jObject.Add('LINK5NAME', FieldByName('Link5Name').AsString);
    jObject.Add('LINK5TEL', FieldByName('Link5Tel').AsString);
    jObject.Add('LINK5TELEXT', FieldByName('Link5TelExt').AsString);
    jObject.Add('LINK5MOBILE', FieldByName('Link5Mobile').AsString);
    jObject.Add('LINK5EMAIL', FieldByName('Link5Email').AsString);
    jObject.Add('LINK5MEMO', FieldByName('Link5MEMO').AsString);
    // 6
    jObject.Add('LINK6TYPE', FieldByName('Link6Type').AsString);
    jObject.Add('LINK6NAME', FieldByName('Link6Name').AsString);
    jObject.Add('LINK6TEL', FieldByName('Link6Tel').AsString);
    jObject.Add('LINK6TELEXT', FieldByName('Link6TelExt').AsString);
    jObject.Add('LINK6MOBILE', FieldByName('Link6Mobile').AsString);
    jObject.Add('LINK6EMAIL', FieldByName('Link6Email').AsString);
    jObject.Add('LINK6MEMO', FieldByName('Link6MEMO').AsString);
    // 7
    jObject.Add('LINK7TYPE', FieldByName('Link7Type').AsString);
    jObject.Add('LINK7NAME', FieldByName('Link7Name').AsString);
    jObject.Add('LINK7TEL', FieldByName('Link7Tel').AsString);
    jObject.Add('LINK7TELEXT', FieldByName('Link7TelExt').AsString);
    jObject.Add('LINK7MOBILE', FieldByName('Link7Mobile').AsString);
    jObject.Add('LINK7EMAIL', FieldByName('Link7Email').AsString);
    jObject.Add('LINK7MEMO', FieldByName('Link7MEMO').AsString);
    // 8
    jObject.Add('LINK8TYPE', FieldByName('Link8Type').AsString);
    jObject.Add('LINK8NAME', FieldByName('Link8Name').AsString);
    jObject.Add('LINK8TEL', FieldByName('Link8Tel').AsString);
    jObject.Add('LINK8TELEXT', FieldByName('Link8TelExt').AsString);
    jObject.Add('LINK8MOBILE', FieldByName('Link8Mobile').AsString);
    jObject.Add('LINK8EMAIL', FieldByName('Link8Email').AsString);
    jObject.Add('LINK8MEMO', FieldByName('Link8MEMO').AsString);
  end;
  DisIndexQueryList(idx);
  gzs := TFun.GzpCompress(jObject.AsJSON) + '$CIF#';
  iocPost(soc, PChar(gzs), Length(gzs));
end;



end.

